package com.gg.jdbc.generate;

import com.gg.jdbc.DB2Connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class SysIndexes {
	
	protected static final String DELIM_LINE = System.getProperty("line.separator");
	
	public static final String DUMMY_INDEX = " ";
	
	protected Connection connection;
	
	protected IndexColumn[] index;
	
	protected Map<String, IndexColumn[]> indexes;
	
	protected ResultSet rs;
	
	protected String creator;
	protected String table;
	
	public SysIndexes(Connection connection, String creator, String table) {
		this.connection = connection;
		this.creator    = creator;
		this.table      = table;
	}
	
	public boolean execSQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT B.IXNAME, B.COLSEQ, B.COLNAME, C.COLTYPE, C.LENGTH, C.SCALE ");
		sb.append("  FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B, SYSIBM.SYSCOLUMNS C ");
		sb.append("  WHERE A.TBNAME  = ? ");
		sb.append("    AND A.CREATOR = ? ");
		sb.append("    AND A.NAME    = B.IXNAME ");
		sb.append("    AND A.CREATOR = B.IXCREATOR ");
		sb.append("    AND A.TBNAME  = C.TBNAME ");
		sb.append("    AND A.CREATOR = C.TBCREATOR ");
		sb.append("    AND B.COLNAME = C.NAME ");
		sb.append("  ORDER BY B.IXNAME, B.COLSEQ ");
		
		try {
			PreparedStatement s = connection.prepareStatement(sb.toString());
			s.setString(1, table);
			s.setString(2, creator);
			
			rs      = s.executeQuery();
			getResultData();
			
			s.close();
			
			return true;
		} catch (SQLException e) {
			DB2Connection.sqlException(e);
		}
		
		return false;
	}

	protected void getResultData() throws SQLException {
		indexes = new TreeMap<String, IndexColumn[]>();
		List<IndexColumn> list = new ArrayList<IndexColumn>();
		String ixname = DUMMY_INDEX;
		while (rs.next()) {
			String nextIxname = rs.getString("IXNAME");
			if (!(ixname.equals(nextIxname))) {
				if (!(ixname.equals(DUMMY_INDEX))) {
					index = (IndexColumn[]) list.toArray(new IndexColumn[list.size()]);
					indexes.put(ixname, index);
					list  = new ArrayList<IndexColumn>();
				}
				ixname = nextIxname;
			}
			IndexColumn c = new IndexColumn();
			c.setColumnName(rs.getString("COLNAME"));
			c.setColumnType(rs.getString("COLTYPE"));
			c.setLength(rs.getInt("LENGTH"));
			c.setScale(rs.getInt("SCALE"));
			list.add(c);
		}
		if (list.size() <= 0)
			index = null;
		else
			index = (IndexColumn[]) list.toArray(new IndexColumn[list.size()]);
		indexes.put(ixname, index);
	}
	
	public Map<String, IndexColumn[]> getIndexes() {
		return indexes;
	}
	
	public StringBuffer getStringBufferWhere(String key) {
		IndexColumn[] index = indexes.get(key);
		StringBuffer sb = new StringBuffer();
		String lineStart = "\t\tsb.append(\"";
		String lineEnd   = " \");";
		String field     = "  WHERE ";
		
		for (int i = 0; i < index.length; i++) {
			sb.append(lineStart);
			sb.append(field);
			sb.append(index[i].getColumnName());
			sb.append(" = ?");
			sb.append(lineEnd);
			sb.append(DELIM_LINE);
			field = "    AND ";
		}
		
		return sb;
	}
	
	public String[] getStringArrayWhere(String key) {
		IndexColumn[] index = indexes.get(key);
		List<String> list = new ArrayList<String>();
		String field     = "  WHERE ";
		for (int i = 0; i < index.length; i++) {
			StringBuffer sb = new StringBuffer();
			sb.append(field);
			sb.append(index[i].getColumnName());
			sb.append(" = ?");
			field = "    AND ";
			list.add(sb.toString());
		}
		return (String[]) list.toArray(new String[list.size()]);
	}
	
	public StringBuffer getStringBufferParameters(String key) {
		IndexColumn[] index = indexes.get(key);
		StringBuffer sb = new StringBuffer();
		
		for (int i = 0; i < index.length; i++) {
			if (i > 0) sb.append(", ");
			sb.append(index[i].getJavaColumnType());
			sb.append(" ");
			sb.append(index[i].getJavaColumnName());
		}
		
		return sb;
	}
	
	public StringBuffer getStringBufferVariables(String key) {
		IndexColumn[] index = indexes.get(key);
		StringBuffer sb = new StringBuffer();
		
		for (int i = 0; i < index.length; i++) {
			if (i > 0) sb.append(", ");
			sb.append(index[i].getJavaColumnName());
		}
		
		return sb;
	}
	
	public class IndexColumn {
		protected String columnName;
		protected String columnType;
		protected int length;
		protected int scale;
		
		public void setColumnName(String columnName) {
			this.columnName = columnName;
		}
		
		public void setColumnType(String columnType) {
			this.columnType = columnType;
		}
		
		public void setLength(int length) {
			this.length = length;
		}
		
		public void setScale(int scale) {
			this.scale = scale;
		}
		
		public String getColumnName() {
			return columnName;
		}
		
		public String getColumnType() {
			return columnType;
		}
		
		public int getLength() {
			return length;
		}
		
		public int getScale() {
			return scale;
		}
		
		public String getJavaColumnName() {
			String temp = columnName.toLowerCase();
			StringBuffer sb = new StringBuffer();
			boolean capitalize = false;
			for (int i = 0; i < temp.length(); i++) {
				char c = temp.charAt(i);
				if (c == '_') {
					capitalize = true;
				} else {
					if (capitalize && c > 96) c -= 32;
					sb.append(c);
					capitalize = false;
				}
			}
			return sb.toString();
		}
		
		public String getJavaColumnType() {
			if (columnType.startsWith("CHAR")) return "String";
			if (columnType.startsWith("VARCHAR")) return "String";
			if (columnType.startsWith("INTEGER")) return "int";
			if (columnType.startsWith("SMALLINT")) return "short";
			if (columnType.startsWith("DECIMAL")) return "java.math.BigDecimal";
			if (columnType.startsWith("DATE")) return "java.sql.Date";
			if (columnType.startsWith("TIME")) return "java.sql.Time";
			if (columnType.startsWith("TIMESTMP")) return "java.sql.Timestamp";
			return columnType;
		}
		
	}

}
